package vlms.dao;

import java.sql.*;
import java.sql.Date;
import java.util.*;

import vlms.bean.PremiumCustomer;
import vlms.exception.PremiumcustomerException;
import vlms.util.DateConvert;

public class PremiumcustomerDAO {

	static HashMap<String, PremiumCustomer> primaryList = new HashMap<String, PremiumCustomer>();
	static HashMap<Integer, PremiumCustomer> personIdList = new HashMap<Integer, PremiumCustomer>();
	static long newMembershipNo = 20000001;
	
	private static void initializeList(Connection con) {
		try {
			PreparedStatement stmt = null;
			ResultSet rs = null;

			String SQL_STATEMENT = "Select * from premiumcustomer";
			stmt = con.prepareStatement(SQL_STATEMENT);
			rs = stmt.executeQuery();
			while (rs.next()) {
				PremiumCustomer dto = new PremiumCustomer();
				staticPopulateVO(dto, rs);
				primaryList.put(dto.getMembershipno(), dto);
				personIdList.put(new Integer(dto.getPersonid()), dto);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private static void initializeMembershipNo(Connection con){
		try {
			PreparedStatement stmt = null;
			ResultSet rs = null;
			String sMembershipNo = null;

			String SQL_STATEMENT = "Select membershipNo from PremiumCustomer where personId IN (Select max(personId) from PremiumCustomer)";
			stmt = con.prepareStatement(SQL_STATEMENT);
			rs = stmt.executeQuery();
			while (rs.next()) {
				sMembershipNo = rs.getString("membershipNo");				
			}
			
			if(sMembershipNo != null) {
				String[] temp = sMembershipNo.split("-");
				sMembershipNo = "";
				for(int i=0;i<temp.length;i++) {
					sMembershipNo += temp[i];
				}
				newMembershipNo = Long.parseLong(sMembershipNo) + 1;
			}
			
			System.out.println("Initialize mem no: " +newMembershipNo);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	static {
		Connection con = null;
		try {
			con = DriverManager.getConnection("jdbc:user_mysql:");
			initializeList(con);
			initializeMembershipNo(con);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	private static void staticPopulateVO(PremiumCustomer dto, ResultSet rs)
			throws SQLException {
		dto.setMembershipno(rs.getString("membershipNo"));
		dto.setSubscriptionfee(rs.getInt("subscriptionFee"));
		dto.setOutstandingmovies(rs.getInt("outStandingMovies"));
		dto.setPersonid(rs.getInt("personId"));
		dto.setSubscriptiondate(rs.getDate("subscriptionDate"));
		dto.setDuedate(rs.getDate("dueDate"));
		dto.setAutopay(rs.getBoolean("autoPay"));
	}
	
	public int delete(String membershipNo, Connection con)
			throws PremiumcustomerException, SQLException {
		PreparedStatement ps = null;
		PremiumCustomer pc = findByPrimaryKey(membershipNo, con);
		ps = con.prepareStatement("delete from  PREMIUMCUSTOMER where membershipNo = ?");
		ps.setString(1, membershipNo);
		int result = (ps.executeUpdate());
		if (result <= 0) {
			throw new PremiumcustomerException(
					"Unable to delete the premium customer with member ship number : "
							+ membershipNo);
		}
		primaryList.remove(membershipNo);
		personIdList.remove(pc.getPersonid());

		return result;
	}

	public int update(String membershipNo, PremiumCustomer premiumcustomer,
			Connection con) throws PremiumcustomerException, SQLException {
		PreparedStatement ps = null;
		ps = con.prepareStatement("update PREMIUMCUSTOMER set "
				+ "SUBSCRIPTIONFEE = ? , OUTSTANDINGMOVIES = ? , PERSONID = ? , "
				+ "SUBSCRIPTIONDATE = ? , DUEDATE = ? , AUTOPAY = ?  where "
				+ "membershipNo = ?");
		ps.setInt(1, premiumcustomer.getSubscriptionfee());
		ps.setInt(2, premiumcustomer.getOutstandingmovies());
		ps.setInt(3, premiumcustomer.getPersonid());
		ps.setDate(4, DateConvert.convertUtilToSQLdate(premiumcustomer
				.getSubscriptiondate()));
		ps.setDate(5,
				DateConvert.convertUtilToSQLdate(premiumcustomer.getDuedate()));
		ps.setBoolean(6, premiumcustomer.getAutopay());
		ps.setString(7, membershipNo);

		int result = (ps.executeUpdate());
		if (result <= 0) {
			throw new PremiumcustomerException("Failed to update the "
					+ "simplecustomer with membership number :" + membershipNo);
		}
		primaryList.remove(membershipNo);
		personIdList.remove(premiumcustomer.getPersonid());
		primaryList.put(membershipNo, premiumcustomer);
		personIdList.put(premiumcustomer.getPersonid(), premiumcustomer);
		return result;
	}

	public int insert(PremiumCustomer premiumcustomer, Connection con)
			throws PremiumcustomerException, SQLException {

		
		String temp = Long.toString(newMembershipNo);
		
		System.out.println("temp value: " + temp);
		newMembershipNo++;
		String membershipNo = temp.substring(0, 3) + "-" + temp.substring(3, 5) + "-" + temp.substring(5, 8);
		
		System.out.println("Membership no" + membershipNo);
		
		premiumcustomer.setMembershipno(membershipNo);
		
		PreparedStatement ps = null;
		ps = con.prepareStatement("insert into PREMIUMCUSTOMER(SUBSCRIPTIONFEE, OUTSTANDINGMOVIES, PERSONID, SUBSCRIPTIONDATE, DUEDATE, AUTOPAY, MEMBERSHIPNO) values (?, ?, ?, ?, ?, ?,?)");
		ps.setInt(1, premiumcustomer.getSubscriptionfee());
		ps.setInt(2, premiumcustomer.getOutstandingmovies());
		ps.setInt(3, premiumcustomer.getPersonid());
		ps.setDate(4, DateConvert.convertUtilToSQLdate(premiumcustomer
				.getSubscriptiondate()));
		ps.setDate(5,
				DateConvert.convertUtilToSQLdate(premiumcustomer.getDuedate()));
		ps.setBoolean(6, premiumcustomer.getAutopay());
		ps.setString(7, premiumcustomer.getMembershipno());

		int result = ps.executeUpdate();
		if (result <= 0) {
			throw new PremiumcustomerException(
					"Failed to insert the simple customer record with membership number: "
							+ premiumcustomer.getMembershipno());
		}
		primaryList.put(premiumcustomer.getMembershipno(), premiumcustomer);
		personIdList.put(premiumcustomer.getPersonid(), premiumcustomer);
		return result;
	}

	public PremiumCustomer findByPrimaryKey(String membershipNo, Connection con)
			throws PremiumcustomerException, SQLException {
		PremiumCustomer pm  = primaryList.get(membershipNo);
		
		if (pm != null)
		{
			return pm;
		}
		PreparedStatement stmt = null;
		ResultSet rs = null;
		final String SQLSTATEMENT = "Select membershipNo, subscriptionFee, outStandingMovies, personId, subscriptionDate, dueDate, autoPay from premiumcustomer where membershipNo = ?";
		stmt = con.prepareStatement(SQLSTATEMENT);
		stmt.setString(1, membershipNo);
		rs = stmt.executeQuery();
		pm = fetchSingleResult(rs);
		
		if (pm == null)
		{
			throw new PremiumcustomerException("Cannot find the person with membership number:"+membershipNo);
		}
		else
		{
			primaryList.put(membershipNo, pm);			
		}
		return pm;
	}

	public PremiumCustomer findByPersonid(int personid, Connection con)
			throws PremiumcustomerException, SQLException {
		PreparedStatement stmt = null;
		PremiumCustomer pm = personIdList.get(personid);
		
		if (pm != null)
		{
			return pm;
		}
		
		ResultSet rs = null;
		String SQL_STATEMENT = "Select membershipNo, subscriptionFee, outStandingMovies, personId, subscriptionDate, dueDate, autoPay from premiumcustomer where personId = ? order by personId";
		stmt = con.prepareStatement(SQL_STATEMENT);
		stmt.setInt(1, personid);
		rs = stmt.executeQuery();
		pm = fetchSingleResult(rs);
		if (pm == null)
		{
			throw new PremiumcustomerException("Unable to find the premium customer as requested");
		}
		else
		{
			personIdList.put(personid, pm);
		}
		return pm;
	}

	public PremiumCustomer[] findByDuedate(Date duedate, Connection con)
			throws PremiumcustomerException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String SQL_STATEMENT = "Select membershipNo, subscriptionFee, outStandingMovies, personId, subscriptionDate, dueDate, autoPay from premiumcustomer where dueDate = ? order by dueDate";
		try {
			stmt = con.prepareStatement(SQL_STATEMENT);
			stmt.setDate(1, duedate);
			rs = stmt.executeQuery();
			return fetchMultiResults(rs);
		} catch (SQLException sqle) {
			throw new PremiumcustomerException(sqle);
		} catch (Exception e) {
			throw new PremiumcustomerException(e);
		} finally {
		}
	}

	public PremiumCustomer[] findAll(Connection con)
			throws PremiumcustomerException, SQLException {
		int size = primaryList.size();
		PremiumCustomer[] pm = primaryList.values().toArray(new PremiumCustomer[size]);
		if (pm.length <= 0)
		{
			throw new PremiumcustomerException("No Premium Customer found in the system.");
		}
		
		return pm;
	}

	public PremiumCustomer[] findExecutingUserSelect(String selectStatement,
			Object[] sqlParams, Connection con) throws PremiumcustomerException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		final String SQL_STATEMENT = selectStatement;
		try {
			stmt = con.prepareStatement(SQL_STATEMENT);
			for (int i = 0; i < sqlParams.length; i++) {
				stmt.setObject(i + 1, sqlParams[i]);
			}
			rs = stmt.executeQuery();
			return fetchMultiResults(rs);
		} catch (SQLException sqle) {
			throw new PremiumcustomerException(sqle);
		} catch (Exception e) {
			throw new PremiumcustomerException(e);
		} finally {
		}
	}

	public PremiumCustomer[] findExecutingUserWhere(String whereClause,
			Object[] sqlParams, Connection con) throws PremiumcustomerException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String SQL_SELECT = "Select membershipNo, subscriptionFee, outStandingMovies, personId, subscriptionDate, dueDate, autoPay from premiumcustomer";
		final String SQL_STATEMENT = SQL_SELECT + " where " + whereClause;
		try {
			stmt = con.prepareStatement(SQL_STATEMENT);
			for (int i = 0; i < sqlParams.length; i++) {
				stmt.setObject(i + 1, sqlParams[i]);
			}
			rs = stmt.executeQuery();
			return fetchMultiResults(rs);
		} catch (SQLException sqle) {
			throw new PremiumcustomerException(sqle);
		} catch (Exception e) {
			throw new PremiumcustomerException(e);
		} finally {
		}
	}

	protected PremiumCustomer fetchSingleResult(ResultSet rs)
			throws SQLException {
		if (rs.next()) {
			PremiumCustomer dto = new PremiumCustomer();
			populateVO(dto, rs);
			return dto;
		} else {
			return null;
		}
	}

	protected void populateVO(PremiumCustomer dto, ResultSet rs)
			throws SQLException {
		dto.setMembershipno(rs.getString("membershipNo"));
		dto.setSubscriptionfee(rs.getInt("subscriptionFee"));
		dto.setOutstandingmovies(rs.getInt("outStandingMovies"));
		dto.setPersonid(rs.getInt("personId"));
		dto.setSubscriptiondate(rs.getDate("subscriptionDate"));
		dto.setDuedate(rs.getDate("dueDate"));
		dto.setAutopay(rs.getBoolean("autoPay"));
	}

	protected PremiumCustomer[] fetchMultiResults(ResultSet rs)
			throws SQLException {
		Collection<PremiumCustomer> resultList = new ArrayList<PremiumCustomer>();
		while (rs.next()) {
			PremiumCustomer dto = new PremiumCustomer();
			populateVO(dto, rs);
			resultList.add(dto);
		}
		PremiumCustomer ret[] = new PremiumCustomer[resultList.size()];
		resultList.toArray(ret);
		return ret;
	}

	
}
